using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using database;
using objetos;

namespace dados
{
    [Serializable]
	#region TB_MENU
	/// <summary>
	/// This object represents the properties and methods of a TB_MENU.
	/// </summary>
	public class DAL_TB_MENU
	{		
	
		/// <summary>
		/// Incluir via aplicacao
		/// </summary>	
		public int Incluir(OBJ_TB_MENU obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				DataSet ds = new DataSet();
				List<string> lista = new List<string>();		
	
				db.CreateParameter("@menu", SqlDbType.VarChar, obj.menu);				
				lista.Add("@menu");				
				db.CreateParameter("@url", SqlDbType.VarChar, obj.url);				
				lista.Add("@url");				
				db.CreateParameter("@descricao", SqlDbType.VarChar, obj.descricao);				
				lista.Add("@descricao");				
				db.CreateParameter("@parametro", SqlDbType.VarChar, obj.parametro);				
				lista.Add("@parametro");				
				db.CreateParameter("@id_menu_pai", SqlDbType.Int, obj.id_menu_pai);				
				lista.Add("@id_menu_pai");				
				db.CreateParameter("@ordem", SqlDbType.Int, obj.ordem);				
				lista.Add("@ordem");				
				db.CreateParameter("@sit_ativo", SqlDbType.Bit, obj.sit_ativo);				
				lista.Add("@sit_ativo");				
	
				string query = String.Format("Insert Into tb_menu ({0}) Values ({1}); SELECT SCOPE_IDENTITY()",  string.Join(",",lista.ToArray()).ToString().Replace("@", ""), string.Join(",",lista.ToArray()).ToString());
				ds = db.ExecuteQuery(query);
				
				return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
			}
			catch (Exception)
       		{
            	throw;
        	}
		}
		
		/// <summary>
		/// Update via aplicacao
		/// </summary>			
		public void Alterar(OBJ_TB_MENU obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				List<string> lista = new List<string>();
	
				db.CreateParameter("@id", SqlDbType.Int, obj.id);
				
	
				db.CreateParameter("@menu", SqlDbType.VarChar, obj.menu);
				lista.Add("menu = @menu");
				db.CreateParameter("@url", SqlDbType.VarChar, obj.url);
				lista.Add("url = @url");
				db.CreateParameter("@descricao", SqlDbType.VarChar, obj.descricao);
				lista.Add("descricao = @descricao");
				db.CreateParameter("@parametro", SqlDbType.VarChar, obj.parametro);
				lista.Add("parametro = @parametro");
				db.CreateParameter("@id_menu_pai", SqlDbType.Int, obj.id_menu_pai);
				lista.Add("id_menu_pai = @id_menu_pai");
				db.CreateParameter("@ordem", SqlDbType.Int, obj.ordem);
				lista.Add("ordem = @ordem");
				db.CreateParameter("@sit_ativo", SqlDbType.Bit, obj.sit_ativo);
				lista.Add("sit_ativo = @sit_ativo");
	
				string query = String.Format("Update TB_MENU Set {0} Where id = @id", string.Join(",",lista.ToArray()).ToString());
				db.ExecuteQuery(query);
			}
			catch (Exception)
       		{
            	throw;
        	}
		}	
		
		/// <summary>
		/// Delete viaaplicacao
		/// </summary>	
		public void Excluir(OBJ_TB_MENU obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				StringBuilder queryParameters = new StringBuilder();
				db.CreateParameter("@id", SqlDbType.Int, obj.id);
		
				string query = "Delete tb_menu Where id = @id";			
				db.ExecuteQuery(query);
			}
			catch (Exception)
       		{
            	throw;
        	}
		}
	
	
		/// <summary>
		/// Lista objeto
		/// </summary>						
		public List<OBJ_TB_MENU> SelecionarLista()
    	{
        	try
        	{
				DAO_DATABASE db = new DAO_DATABASE();
				List<OBJ_TB_MENU> lista = new List<OBJ_TB_MENU>();
				OBJ_TB_MENU obj = null;
				DataTable dt = new DataTable();
				DataSet ds = new DataSet();
				StringBuilder sb = new StringBuilder();
				sb.Append("Select * from  TB_MENU ");
        
	            ds = db.ExecuteQuery(sb.ToString());
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					
					foreach (DataRow dr in dt.Rows)
					{											
						obj = new OBJ_TB_MENU();
						Set(obj,dr);
						lista.Add(obj);
		
					}
				}
				return lista;
        	}
        	catch (Exception)
       		{
            	throw;
        	}

    	}
		
		/// <summary>
		/// Set Objeto
		/// </summary>	
		private void Set(OBJ_TB_MENU obj,DataRow dr)
		{
			obj.id = Convert.ToInt32(dr["id"]);						
			obj.menu = dr["menu"] == DBNull.Value ?  string.Empty : dr["menu"].ToString() ;
			obj.url = dr["url"] == DBNull.Value ?  string.Empty : dr["url"].ToString() ;
			obj.descricao = dr["descricao"] == DBNull.Value ?  string.Empty : dr["descricao"].ToString() ;
			obj.parametro = dr["parametro"] == DBNull.Value ?  string.Empty : dr["parametro"].ToString() ;
			obj.id_menu_pai = Convert.ToInt32(dr["id_menu_pai"].ToString()) ;	
			obj.ordem = Convert.ToInt32(dr["ordem"].ToString()) ;	
			obj.sit_ativo = Convert.ToBoolean(dr["sit_ativo"].ToString()) ;
			
		}						
				
		/// <summary>
		/// Selecionar Objeto Por ID
		/// </summary>	
		public OBJ_TB_MENU SelecionarObjetoPorId(int id)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				OBJ_TB_MENU obj = new OBJ_TB_MENU();
				DataSet ds = new DataSet();				
				DataTable dt = new DataTable();
				db.CreateParameter("@id", SqlDbType.Int, id);
				ds = db.ExecuteQuery("SELECT * FROM tb_menu  Where id = @id");				
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					
					foreach (DataRow dr in dt.Rows)
                	{
						obj = new OBJ_TB_MENU();
						Set(obj,dr);																			
					}
					return obj;
					
				}
				else
				{
					return null;	
				}			
			}
			catch (Exception)
       		{
            	throw;
        	}
			
		}
					
		
		
		/// <summary>
		/// Lista objeto Parametro Where OrderBy
		/// </summary>							
		public List<OBJ_TB_MENU> SelecionarLista(String Where, String OrderBy)
    	{
        	try
        	{
				DAO_DATABASE db = new DAO_DATABASE();
				OBJ_TB_MENU obj = null;
				DataSet ds = new DataSet();
				DataTable dt = new DataTable();
				List<OBJ_TB_MENU> lista = new List<OBJ_TB_MENU>();
				
				StringBuilder sb = new StringBuilder();
				db.CreateParameter("@Where",SqlDbType.VarChar, Where );
				db.CreateParameter("@OrderBy", SqlDbType.VarChar, OrderBy );				
				
				sb.Append("SELECT * ");
				sb.Append(" FROM TB_MENU");
				if(Where != string.Empty)				
				sb.Append(string.Format(" Where {0} ",Where));
				if(OrderBy != string.Empty)
				sb.Append(string.Format(" Order by {0}",OrderBy));
				
				ds = db.ExecuteQuery(sb.ToString());
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					foreach (DataRow dr in dt.Rows)
					{						
						obj = new OBJ_TB_MENU();
						Set(obj,dr);			
						lista.Add(obj);
					}
				}
				return lista;
        	}
        	catch (Exception)
       		{
            	throw;
        	}

    	}

		
		public DataSet Selecionar(String Where, String OrderBy, int PageIndex, int PageSize )
		{
				DAO_DATABASE db = new DAO_DATABASE();
				DataSet ds = new DataSet();
				
				StringBuilder sb = new StringBuilder();

                PageIndex = PageSize * PageIndex;
                PageSize = PageIndex + PageSize;
				
		      if (OrderBy == string.Empty) OrderBy = "id";

            	sb.Append("SELECT * FROM ");
            	sb.Append(string.Format(" (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber,",OrderBy));
           		sb.Append(" * FROM   tb_menu ");
				if (Where != string.Empty)
				{
					sb.Append(string.Format(" WHERE {0}", Where));
				}
				sb.Append(" ) query  ");
				sb.Append(string.Format(" WHERE RowNumber BETWEEN {0} + 1 and {1}; ",PageIndex,PageSize));

            	sb.Append("SELECT count(*) FROM tb_menu");
				if (Where != string.Empty)
				{
					sb.Append(string.Format(" WHERE {0}", Where));
				}
				ds = db.ExecuteQuery(sb.ToString());
				
				if (!db.DataSetIsEmpty(ds))
            	{
                publico.PUB_PAGINACAO.CalcularTotalDePaginas(Convert.ToDecimal(ds.Tables[1].Rows[0][0].ToString()));
            	}

				
            	return ds;
								
		}
				
								
		public DataSet Selecionar(String Where, String OrderBy)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * from  TB_MENU ");
            if (Where != string.Empty)
            {
                db.CreateParameter("@Where", SqlDbType.VarChar, Where);
                sb.Append(string.Format(" Where  {0}",Where));
            }

            if (OrderBy != string.Empty)
            {
                db.CreateParameter("@OrderBy", SqlDbType.VarChar, OrderBy);
                sb.Append(string.Format(" Order By {0}", OrderBy));
            }

            ds = db.ExecuteQuery(sb.ToString());
            return ds;
        }		
		
		public DataSet Selecionar()
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * from  TB_MENU Order By id");            
            ds = db.ExecuteQuery(sb.ToString());
            return ds;
        }		
		
		
		public DataSet ListaCampos()
        {

            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT TABLE_NAME, ORDINAL_POSITION, ");
            sb.Append(" COLUMN_NAME, DATA_TYPE, IS_NULLABLE ");
            sb.Append(" FROM INFORMATION_SCHEMA.COLUMNS");
            sb.Append(" WHERE TABLE_NAME = 'tb_menu'");
            ds = db.ExecuteQuery(sb.ToString());

            return ds;

        }

		public List<OBJ_TB_MENU> DropDownList()
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            List<OBJ_TB_MENU> lista = new List<OBJ_TB_MENU>();
            ds = db.ExecuteProcedure("PROC_TB_MENU");

            if (!db.DataSetIsEmpty(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    OBJ_TB_MENU o_tb_menu = new OBJ_TB_MENU();

                    o_tb_menu.menu = dr["menu"].ToString();
                    o_tb_menu.id = Convert.ToInt32(dr["id"].ToString());

                    lista.Add(o_tb_menu);
                }


            }

            return lista;
           
        }

      


          public OBJ_TB_MENU SelecionarObjetoPorIdMenuPai(int id)
          {
              try
              {
                  DAO_DATABASE db = new DAO_DATABASE();
                  OBJ_TB_MENU obj = new OBJ_TB_MENU();
                  DataSet ds = new DataSet();
                  DataTable dt = new DataTable();
                  db.CreateParameter("@id_menu_pai", SqlDbType.Int, id);
                  ds = db.ExecuteQuery("SELECT * FROM tb_menu WHERE id_menu_pai= @id_menu_pai");

                  if (!db.DataSetIsEmpty(ds))
                  {
                      dt = ds.Tables[0];

                      foreach (DataRow dr in dt.Rows)
                      {
                          obj = new OBJ_TB_MENU();
                          obj.id = Convert.ToInt32(dr["id"]);
                          obj.menu = dr["menu"] == DBNull.Value ? "" : dr["menu"].ToString();
                          obj.url = dr["url"] == DBNull.Value ? "" : dr["url"].ToString();
                          obj.descricao = dr["descricao"] == DBNull.Value ? "" : dr["descricao"].ToString();
                          obj.parametro = dr["parametro"] == DBNull.Value ? "" : dr["parametro"].ToString();
                          obj.id_menu_pai = Convert.ToInt32(dr["id_menu_pai"].ToString());
                          obj.ordem = Convert.ToInt32(dr["ordem"].ToString());
                          obj.sit_ativo = Convert.ToBoolean(dr["sit_ativo"].ToString());
                      }
                      return obj;

                  }
                  else
                  {
                      return null;
                  }
              }
              catch (Exception)
              {
                  throw;
              }

          }

          public List<OBJ_TB_MENU> SelecionarListaPorIdMenuPai(int id)
          {
              try
              {
                  DAO_DATABASE db = new DAO_DATABASE();
                  OBJ_TB_MENU obj = new OBJ_TB_MENU();
                  List<OBJ_TB_MENU> lista = new List<OBJ_TB_MENU>();
                  DataSet ds = new DataSet();
                  DataTable dt = new DataTable();
                  db.CreateParameter("@id_menu_pai", SqlDbType.Int, id);
                  ds = db.ExecuteQuery("SELECT * FROM tb_menu WHERE id_menu_pai= @id_menu_pai");

                  if (!db.DataSetIsEmpty(ds))
                  {
                      dt = ds.Tables[0];

                      foreach (DataRow dr in dt.Rows)
                      {
                          obj = new OBJ_TB_MENU();
                          obj.id = Convert.ToInt32(dr["id"]);
                          obj.menu = dr["menu"] == DBNull.Value ? "" : dr["menu"].ToString();
                          obj.url = dr["url"] == DBNull.Value ? "" : dr["url"].ToString();
                          obj.descricao = dr["descricao"] == DBNull.Value ? "" : dr["descricao"].ToString();
                          obj.parametro = dr["parametro"] == DBNull.Value ? "" : dr["parametro"].ToString();
                          obj.id_menu_pai = Convert.ToInt32(dr["id_menu_pai"].ToString());
                          obj.ordem = Convert.ToInt32(dr["ordem"].ToString());
                          obj.sit_ativo = Convert.ToBoolean(dr["sit_ativo"].ToString());
                          lista.Add(obj);
                      }
                      return lista;

                  }
                  else
                  {
                      return null;
                  }
              }
              catch (Exception)
              {
                  throw;
              }

          }

          public List<OBJ_TB_MENU> ListaMenuPai(Int32 id_usuario)
          {
              StringBuilder sb = new StringBuilder();
              DAO_DATABASE db = new DAO_DATABASE();
              List<OBJ_TB_MENU> listaObj = new List<OBJ_TB_MENU>();
              List<String> listaStr = new List<string>();
              OBJ_TB_MENU obj = null;
              DataSet ds = new DataSet();
              DataTable dt = new DataTable();

              sb.Append("SELECT * ");
              sb.Append(" FROM tb_menu");
              sb.Append(" WHERE id in (SELECT id_menu FROM tb_menu_usuario WHERE id_usuario = @id_usuario )");
              sb.Append(" AND id_menu_pai = 0 ");
              sb.Append(" AND sit_ativo = 'true' ORDER BY ordem");

              db.CreateParameter("@id_usuario", SqlDbType.Int, id_usuario);
              ds = db.ExecuteQuery(sb.ToString());

              if (!db.DataSetIsEmpty(ds))
              {

                  dt = ds.Tables[0];

                  foreach (DataRow dr in dt.Rows)
                  {
                      obj = new OBJ_TB_MENU();
                      obj.id = Convert.ToInt32(dr["id"].ToString());
                      obj.menu = dr["menu"].ToString();
                      obj.descricao = dr["descricao"].ToString();
                      obj.url = dr["url"].ToString();
                      obj.parametro = dr["parametro"].ToString();
                      obj.id_menu_pai = Convert.ToInt32(dr["id_menu_pai"].ToString());
                      obj.ordem = Convert.ToInt32(dr["ordem"].ToString());
                      obj.sit_ativo = Convert.ToBoolean(dr["sit_ativo"].ToString());

                      listaObj.Add(obj);
                  }
              }
              return listaObj;
          }

          public List<OBJ_TB_MENU> ListaMenuFilho(Int32 id_menu, Int32 id_usuario)
          {
              StringBuilder sb = new StringBuilder();
              DAO_DATABASE db = new DAO_DATABASE();
              List<OBJ_TB_MENU> listaObj = new List<OBJ_TB_MENU>();
              OBJ_TB_MENU obj = null;
              DataSet ds = new DataSet();
              DataTable dt = new DataTable();


              sb.Append("SELECT * ");
              sb.Append(" FROM tb_menu");
              sb.Append(" WHERE id in (SELECT id_menu FROM tb_menu_usuario WHERE id_usuario = @id_usuario )");
              sb.Append(" AND id_menu_pai = @id_menu");
              sb.Append(" AND sit_ativo = 'true' ORDER BY ordem");

              db.CreateParameter("@id_menu", SqlDbType.Int, id_menu);
              db.CreateParameter("@id_usuario", SqlDbType.Int, id_usuario);
              ds = db.ExecuteQuery(sb.ToString());

              if (!db.DataSetIsEmpty(ds))
              {

                  dt = ds.Tables[0];

                  foreach (DataRow dr in dt.Rows)
                  {
                      obj = new OBJ_TB_MENU();
                      obj.id = Convert.ToInt32(dr["id"].ToString());
                      obj.menu = dr["menu"].ToString();
                      obj.descricao = dr["descricao"].ToString();
                      obj.url = dr["url"].ToString();
                      obj.parametro = dr["parametro"].ToString();
                      obj.id_menu_pai = Convert.ToInt32(dr["id_menu_pai"].ToString());
                      obj.ordem = Convert.ToInt32(dr["ordem"].ToString());
                      obj.sit_ativo = Convert.ToBoolean(dr["sit_ativo"].ToString());

                      listaObj.Add(obj);
                  }
              }
              return listaObj;
          }

          public List<OBJ_TB_MENU> ListaMenuPorIdUsuario(int id_usuario)
          {
              DAO_DATABASE db = new DAO_DATABASE();
              DataSet ds = new DataSet();
              OBJ_TB_MENU obj = null;
              DataTable dt = new DataTable();
              List<OBJ_TB_MENU> lista = new List<OBJ_TB_MENU>();
              db.CreateParameter("@id_usuario", SqlDbType.Int,@id_usuario);
              ds = db.ExecuteProcedure("PROC_TB_MENU_USUARIO");
              
               if (!db.DataSetIsEmpty(ds))
              {

                  dt = ds.Tables[0];

                  foreach (DataRow dr in dt.Rows)
                  {
                      obj = new OBJ_TB_MENU();
                      obj.id = Convert.ToInt32(dr["id"].ToString());
                      obj.menu = dr["menu"].ToString();                  
                      lista.Add(obj);
                  }
              }
              return lista;
              
          }

	}
	#endregion
}

